CREATE VIEW [dbo].[vGroupLinkReport]
AS
SELECT o.OpportunityId, o2.OwnerName AS RegionalManager, o3.OwnerName AS Salesman, o.OpportunityOwnerGroupKey, o.OpportunityKey
FROM (SELECT DISTINCT OpportunityId, OpportunityOwnerGroupKey, OpportunityKey
      FROM  dbo.vOpportunityReport
      WHERE (OwnerRole = 'Regional Manager') OR (OwnerRole = 'Salesperson')) AS o LEFT OUTER JOIN
           dbo.vOpportunityReport AS o2 ON o.OpportunityOwnerGroupKey = o2.OpportunityOwnerGroupKey AND
            o2.OwnerRole = 'Regional Manager' LEFT OUTER JOIN
            dbo.vOpportunityReport AS o3 ON o.OpportunityOwnerGroupKey = o3.OpportunityOwnerGroupKey AND o3.OwnerRole = 'Salesperson'

GO